<?php
declare (strict_types=1);

namespace app\common\model;

use Exception;
use think\Collection;
use think\facade\Db;

class FeedbackForm extends CommonBase
{
    private const CREATE_MAIN_TABLE_SQL = <<<str
CREATE TABLE `%sfeedback_%s` (  
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,  
	`title` varchar(255)  NOT NULL DEFAULT ''  COMMENT '标题',  
	`status` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '状态 1启用 2禁用',  
	`created_at` datetime DEFAULT NULL COMMENT '创建时间',  
	`updated_at` datetime DEFAULT NULL COMMENT '更新时间',  
	`is_delete` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '1未删除 2已删除'
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='%s'
str;

    private const CREATE_VARCHAR_TABLE_SQL = <<<str
CREATE TABLE `%sfeedback_%s_varchar` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `feedback_id` int unsigned NOT NULL DEFAULT '0' COMMENT '文档ID',
  `field_id` int unsigned NOT NULL DEFAULT '0' COMMENT '字段ID',
  `field_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '字段值',
  `is_delete` int unsigned NOT NULL DEFAULT '0' COMMENT '1未删除 2已删除',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `key_field_id` (`field_id`) USING BTREE,
  KEY `key_feedback_id` (`feedback_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='%s';
str;

    private const CREATE_TEXT_TABLE_SQL = <<<str
CREATE TABLE `%sfeedback_%s_text` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `feedback_id` int unsigned NOT NULL DEFAULT '0' COMMENT '文档ID',
  `field_id` int unsigned NOT NULL DEFAULT '0' COMMENT '字段ID',
  `field_value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '字段值',
  `is_delete` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '1未删除 2已删除',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `key_field_id` (`field_id`) USING BTREE,
  KEY `key_feedback_id` (`feedback_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='%s';
str;


    private const DROP_TABLE_SQL = "DROP TABLE IF EXISTS `%s`";

    public function __construct(array $data = [], string $connection = 'mysql')
    {
        parent::__construct($data);
        $this->connection = $connection;
    }

    public function getAllFeedbackForm(array $where = []): Collection
    {
        $where['is_delete'] = config('status.mysql.table_normal');
        return $this->where($where)->select();
    }

    public function getFeedbackFormList(int $page = 1, int $limit = 10, array $where = []): Collection
    {
        $where['is_delete'] = config('status.mysql.table_normal');
        $offset             = ($page - 1) * $limit;
        return $this->where($where)->limit($offset, $limit)->select();
    }

    public function getFeedbackFormCount(array $where = []): int
    {
        $where['is_delete'] = config('status.mysql.table_normal');
        return $this->where($where)->count();
    }

    public function findFeedbackFormById(int $id)
    {
        return $this->where(['id' => $id, 'is_delete' => config('status.mysql.table_normal')])->find();
    }

    public function findFeedbackFormByCode(string $code)
    {
        return $this->where(['form_code' => $code, 'is_delete' => config('status.mysql.table_normal')])->find();
    }

    /**
     * @throws Exception
     */
    public function addFeedbackForm(array $data): bool
    {
        unset($data['website_code']);
        try {
            $feedbackFormId = 0;
            $this->startTrans();
            $formObj = $this->where(['is_delete' => config('status.mysql.table_delete'), 'form_code' => $data['form_code']])->find();
            if (!empty($formObj)) {
                $form = $formObj->toArray();
                $this->where(['id' => $form['id']])->save([
                    'name'       => $data['name'],
                    'created_at' => date('Y-m-d H:i:s'),
                    'updated_at' => date('Y-m-d H:i:s'),
                    'is_delete'  => config('status.mysql.table_normal')]);
                $feedbackFormId = $form['id'];
            } else {
                $this->save($data);
                $feedbackFormId = $this->getLastInsID();
            }

            $status = config('status.mysql.table_normal');
            $fields = [
                ['name' => '留言标题', 'code' => 'title', 'field_type' => 'input', 'status' => $status, 'is_system' => self::FIELD_IS_SYSTEM, 'is_require' => self::FIELD_IS_REQUIRE, 'show_list' => self::FIELD_IS_SHOW_LIST, 'sort' => 1],
                ['name' => '留言状态', 'code' => 'status', 'field_type' => 'radio', 'status' => $status, 'is_system' => self::FIELD_IS_SYSTEM, 'is_require' => self::FIELD_IS_REQUIRE, 'show_list' => self::FIELD_IS_HIDDEN_LIST, 'sort' => 2],
                ['name' => '留言时间', 'code' => 'created_at', 'field_type' => 'datetime', 'status' => $status, 'is_system' => self::FIELD_IS_SYSTEM, 'is_require' => self::FIELD_IS_NOT_REQUIRE, 'show_list' => self::FIELD_IS_SHOW_LIST, 'sort' => 3],
                ['name' => '留言内容', 'code' => 'content', 'field_type' => 'textarea', 'status' => $status, 'is_system' => self::FIELD_IS_CUSTOMER, 'is_require' => self::FIELD_IS_NOT_REQUIRE, 'show_list' => self::FIELD_IS_HIDDEN_LIST, 'sort' => 4],
            ];

            foreach ($fields as $field) {
                $insertData = array_merge(
                    $field,
                    [
                        'form_id'    => $feedbackFormId,
                        'is_delete'  => config('status.mysql.table_normal'),
                        'created_at' => date('Y-m-d H:i:s'),
                        'updated_at' => date('Y-m-d H:i:s'),
                    ]);

                $row = $this->name('feedback_field')->where(['is_delete' => config('status.mysql.table_delete'), 'form_id' => $feedbackFormId, 'code' => $field['code']])->find();
                if (!empty($row)) {
                    $existFeedbackField = $row->toArray();
                    $this->name( 'feedback_field')->where(['id' => $existFeedbackField['id']])->save(
                        array_merge($existFeedbackField, $insertData)
                    );

                    continue;
                }

                $this->name('feedback_field')->insert($insertData);
            }

            $this->commit();

            return true;
        } catch (Exception $e) {
            $this->rollback();
            throw new Exception($e->getMessage());
        }
    }

    public function updateFeedbackForm(int $id, array $data): bool
    {
        $data['updated_at'] = date('Y-m-d H:i:s');
        return (boolean)$this->where(['id' => $id])->save($data);;
    }

    /**
     * @throws Exception
     */
    public function deleteFeedbackForm(int $id): bool
    {
        try {
            $this->startTrans();
            $this->where(['id' => $id])->save(['is_delete' => config('status.mysql.table_delete')]);
            $this->name(  'feedback_field')->where(['form_id' => $id])->save(['is_delete' => config('status.mysql.table_delete')]);

            $this->commit();
            return true;
        } catch (Exception $e) {
            $this->rollback();
            throw new Exception($e->getMessage());
        }
    }

    public function createTable(array $data): bool
    {
        $tables = [
            self::CREATE_MAIN_TABLE_SQL,
            self::CREATE_TEXT_TABLE_SQL,
            self::CREATE_VARCHAR_TABLE_SQL,

        ];
        foreach ($tables as $tableSql) {
            $sql = sprintf($tableSql, config('database.connections.' . $this->connection . '.prefix'), $data['form_code'], $data['name']);
            Db::connect($this->connection)->execute($sql);
        }

        return true;
    }

    public function dropTable(array $data): bool
    {
        $tables = [
            "%sfeedback_%s",
            "%sfeedback_%s_text",
            "%sfeedback_%s_varchar",
        ];

        foreach ($tables as $table) {
            $sql = sprintf(self::DROP_TABLE_SQL, sprintf($table, config('database.connections.' . $this->connection . '.prefix'), $data['form_code']));
            Db::connect($this->connection)->execute($sql);
        }

        return true;
    }
}
